﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Luminji.Core.Data;
using Luminji.Core.Data;
using Luminji.ExamWeb.Core.Domain;
using Luminji.ExamWeb.Core.Domain.Model;

namespace Luminji.ExamWeb.Infrastructure.DataAccess.Impl.SqlServer
{
    public class PaperRepository : IPaperRepository
    {
        SubjectRepository _sb = new SubjectRepository();

        public IList<Core.Domain.Model.Paper> GetPapersBySubjectCode(string subjectCode)
        {
            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, "select * from Paper where SubjectCode='" + subjectCode + "'"))
            {
                return ds.Tables[0].ToList<Paper>();
            }
        }

        public int GetPapersNumsBySubjectAndChildrenCode(string subjectCode)
        {
            string sqlWhere = InitWhere(subjectCode);
            string sql = string.Format(
                @"SELECT count(*) FROM Paper WHERE SubjectCode IN ({0})", sqlWhere);
            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                int i = (int)ds.Tables[0].Rows[0][0];
                return i / 10 + 1;
            }
        }

        public IList<Paper> GetPapersBySubjectAndChildrenCodeInPage(string subjectCode, int pageNum)
        {
            if (pageNum < 1)
            {
                pageNum = 1;
            }
            string sqlWhere = InitWhere(subjectCode);
            string sql = string.Format(
                @"
                WITH x AS
                (
                SELECT ROW_NUMBER() OVER(ORDER BY PaperName DESC) AS 'RowNumber', * FROM Paper
                WHERE SubjectCode IN ({0})
                )
                SELECT * FROM x where RowNumber between {1} and {2}", sqlWhere, 10 * (pageNum - 1) + 1, 10 * pageNum);
            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                IList<Paper> res = ds.Tables[0].ToList<Paper>();
                return res;
            }
        }

        public IList<Core.Domain.Model.Paper> GetPapersBySubjectAndChildrenCode(string subjectCode)
        {
            string sqlWhere = InitWhere(subjectCode);

            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, "select * from Paper where SubjectCode in (" + sqlWhere + ")"))
            {
                IList<Paper> res = ds.Tables[0].ToList<Paper>();
                return res;
            }
        }


        private string InitWhere(string subjectCode)
        {
            var subjects = _sb.GetSubjectAll();
            var subjectCodes = from p in subjects
                               where p.SubjectCode == subjectCode || p.ParentCode == subjectCode
                               select p.SubjectCode;
            string sqlWhere = subjectCodes.Aggregate(string.Empty, (current, code) => current + string.Format(@"'{0}',", code));
            return sqlWhere.Substring(0, sqlWhere.Length - 1);
        }

        public Core.Domain.Model.Paper GetPaperByCode(string paperCode)
        {
            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, "select * from Paper where PaperCode='" + paperCode + "'"))
            {
                IList<Paper> res = ds.Tables[0].ToList<Paper>();
                return res.First();
            }
        }

        /// <summary>
        /// 获取一份完整的试卷，并且初始化所有的内容
        /// </summary>
        /// <param name="paperCode"></param>
        /// <returns></returns>
        public Paper GetPaperWithDetailDetailByPaperCode(string paperCode)
        {
            string sql = @"select *
				from PaperDetail a
                LEFT JOIN dbo.QuestionFace h ON a.QuestionFaceCode = h.QuestionFaceCode
				LEFT JOIN dbo.QuestionUnit b ON a.QuestionUnitCode = b.QuestionUnitCode
            	LEFT JOIN dbo.QuestionUnitBase c ON b.QuestionUnitBaseCode = c.QuestionUnitBaseCode
                LEFT JOIN dbo.Paper d ON a.PaperCode = d.PaperCode
                LEFT JOIN dbo.PaperTemplate e ON d.PaperTemplateCode = e.PaperTemplateCode
				LEFT JOIN dbo.QuestionStem f ON f.QuestionStemCode = a.QuestionStemCode
				LEFT JOIN dbo.PaperTemplatePart g ON b.QuestionUnitCode = g.QuestionUnitCode AND e.PaperTemplateCode=g.PaperTemplateCode
             where a.PaperCode='" + paperCode + @"' 
             ORDER BY a.QuestionNo";
            Paper paper = new Paper { PaperTemplate = new PaperTemplate() };
            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                paper.PaperDetails = ds.Tables[0].ToList<PaperDetail>();
            }
            GetPaperChoicOptionsAndSetToPaper(paper.PaperDetails);
            paper.PaperTemplate.PaperTemplateParts = GetPaperTemplatePart(paper.PaperDetails);
            return paper;
        }

        private void GetPaperChoicOptionsAndSetToPaper(IList<PaperDetail> details)
        {
            string strWhere = details.Aggregate(string.Empty, (current, paperDetail) => current + ("'" + paperDetail.QuestionStemCode + "',"));
            string sql = @"select * from dbo.QuestionOptions where QuestionStemCode in (" +
                         strWhere.Substring(0, strWhere.Length - 1) + ")";
            IList<QuestionOption> options = null;
            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                options = ds.Tables[0].ToList<QuestionOption>();
            }
            foreach (PaperDetail paperDetail in details)
            {
                var currentDetailOprations = from p in options where p.QuestionStemCode == paperDetail.QuestionStemCode select p;
                foreach (QuestionOption currentDetailOpration in currentDetailOprations)
                {
                    paperDetail.QuestionOptions.Add(currentDetailOpration);
                }
            }
        }

        IEnumerable<PaperTemplatePart> GetPaperTemplatePart(IList<PaperDetail> details)
        {
            IEnumerable<PaperTemplatePart> parts = null;
            parts = (from p in details
                     select new PaperTemplatePart()
                     {
                         PaperTemplatePartCode = p.PaperTemplatePartCode,
                         PaperTemplatePartName = p.PaperTemplatePartName
                     }).Distinct();
            return parts;
        }
    }
}

class Temp1
{
    //IEnumerable<PaperTemplatePart> GetPaperTemplatePart(EnumerableRowCollection<DataRow> onePaper)
    //{
    //    IEnumerable<PaperTemplatePart> parts = null;
    //    parts = (from p in onePaper
    //             select new PaperTemplatePart()
    //             {
    //                 PaperTemplatePartCode = p.Field<string>("PaperTemplatePartCode"),
    //                 PaperTemplatePartName = p.Field<string>("PaperTemplatePartName")
    //             }).Distinct();
    //    return parts;
    //}   

    //--获取一份试卷
    //select d.PaperName, e.PaperTemplateName, a.QuestionNo AS QuestionNoo,f.PaperTemplatePartName, b.QuestionUnitName AS QuestionUnitNamee,
    // c.QuestionUnitBaseName AS QuestionUnitBaseNamee, a.QuestionFaceCode AS QuestionFaceCodee, 
    //a.QuestionStemCode AS QuestionStemCodee, a.QuestionUnitCode AS xx 
    //    from PaperDetail a
    //    LEFT JOIN dbo.QuestionUnit b ON a.QuestionUnitCode = b.QuestionUnitCode
    //    LEFT JOIN dbo.QuestionUnitBase c ON b.QuestionUnitBaseCode = c.QuestionUnitBaseCode
    //    LEFT JOIN dbo.Paper d ON a.PaperCode = d.PaperCode
    //    LEFT JOIN dbo.PaperTemplate e ON d.PaperTemplateCode = e.PaperTemplateCode
    //    LEFT JOIN dbo.PaperTemplatePart f ON b.QuestionUnitCode = f.QuestionUnitCode AND e.PaperTemplateCode=f.PaperTemplateCode
    // where a.PaperCode='4CF4798650D34D3BA0F377FDB8ECCD87' 
    // ORDER BY a.QuestionNo

    // --获取试卷模版的结构
    // SELECT a.PaperTemplatePartName, c.PaperTemplateName,  * FROM dbo.PaperTemplatePart a 
    //    LEFT JOIN QuestionUnit b ON a.QuestionUnitCode = b.QuestionUnitCode
    //    LEFT JOIN dbo.PaperTemplate c ON a.PaperTemplateCode = c.PaperTemplateCode
    // WHERE a.PaperTemplateCode ='121288aaa0714621834ed8ee95d3510f'
    // ORDER BY OrderValue

    //            string sql = @"select d.PaperName AS PaperName, 
    //				e.PaperTemplateName AS PaperTemplateName, 
    //				a.QuestionNo AS QuestionNo,
    //				f.PaperTemplatePartCode AS PaperTemplatePartCode, f.PaperTemplatePartName AS PaperTemplatePartName,
    //				b.QuestionUnitName AS QuestionUnitName,
    //				c.QuestionUnitBaseName AS QuestionUnitBaseName, 
    //				a.QuestionFaceCode AS QuestionFaceCode, 
    //				a.QuestionStemCode AS QuestionStemCode, 
    //				a.QuestionUnitCode AS QuestionUnitCode 
    //				from PaperDetail a
    //                LEFT JOIN dbo.QuestionUnit b ON a.QuestionUnitCode = b.QuestionUnitCode
    //            	LEFT JOIN dbo.QuestionUnitBase c ON b.QuestionUnitBaseCode = c.QuestionUnitBaseCode
    //                LEFT JOIN dbo.Paper d ON a.PaperCode = d.PaperCode
    //                LEFT JOIN dbo.PaperTemplate e ON d.PaperTemplateCode = e.PaperTemplateCode
    //				LEFT JOIN dbo.PaperTemplatePart f ON b.QuestionUnitCode = f.QuestionUnitCode AND e.PaperTemplateCode=f.PaperTemplateCode
    //             where a.PaperCode='4CF4798650D34D3BA0F377FDB8ECCD87' 
    //             ORDER BY a.QuestionNo";
    //            EnumerableRowCollection<DataRow> onePaper = null;
    //            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, sql))
    //            {
    //                onePaper = ds.Tables[0].AsEnumerable();
    //            }
    //            Paper paper = new Paper();
    //            paper.PaperTemplate = new PaperTemplate();
    //            paper.PaperTemplate.PaperTemplateParts = GetPaperTemplatePart(onePaper);
    //            return paper;
}
